﻿Imports DTO
Imports System.Windows.Forms
Imports System
Imports System.Text


Public Class SachDAO
    Public Sub Them(ByRef sDTO As SachDTO)
        Try
            Dim db As New QuanLyNhaSachDataContext()
            Dim sach As New Sach

            sach.TenSach = sDTO.TenSach
            sach.MaTL = sDTO.MaTL
            sach.TenTG = sDTO.TenTG
            sach.Soluong = sDTO.SoLuong

            db.Saches.InsertOnSubmit(sach)
            db.SubmitChanges()
            sDTO.MaSach = sach.MaSach
            sDTO.TenTL = sach.TheLoai.TenTL
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
        End Try
    End Sub

    Public Function LayBang() As IQueryable(Of Object)
        Dim db As New QuanLyNhaSachDataContext
        Dim dt As IQueryable(Of Object)
        Dim saches = From p In db.Saches _
                  Select New With {p.TenSach, p.TheLoai.TenTL, p.TenTG, p.Soluong}
        dt = saches
        Return dt
    End Function

    Public Function TimKiem(ByVal sDTO As SachDTO) As IQueryable(Of Sach)
        Dim db As New QuanLyNhaSachDataContext
        Dim books = From s In db.Saches _
                             Where s.TenSach = sDTO.TenSach And s.TenTG = sDTO.TenTG And s.MaTL = sDTO.MaTL _
                             Select s
        Dim saches As IQueryable(Of Sach)
        saches = books
        If saches.Count > 0 Then
            sDTO.MaSach = saches.Single.MaSach
            sDTO.SoLuong = sDTO.SoLuong + saches.Single.Soluong
        End If
        Return saches
    End Function

    Public Function TimKiemTheoTenSach(ByVal tenSach As String) As IQueryable(Of Object)
        Dim db As New QuanLyNhaSachDataContext
        Dim books = (From s In db.Saches _
                    Where s.TenSach Like tenSach _
                    Select New With {s.TenSach, s.TheLoai.TenTL, s.TenTG, s.Soluong})
        Return books
    End Function

    Public Function TimKiemTheoTenTG(ByVal tenTG As String) As IQueryable(Of Object)
        Dim db As New QuanLyNhaSachDataContext
        Dim books = (From s In db.Saches _
                    Where s.TenTG Like tenTG _
                    Select New With {s.TenSach, s.TheLoai.TenTL, s.TenTG, s.Soluong})
        Return books
    End Function

    Public Function TimKiemTheoTheLoai(ByVal maTheLoai As Integer) As IQueryable(Of Object)
        Dim db As New QuanLyNhaSachDataContext
        Dim books = (From s In db.Saches _
                    Where s.MaTL Like maTheLoai _
                    Select New With {s.TenSach, s.TheLoai.TenTL, s.TenTG, s.Soluong})
        Return books
    End Function

    Public Function TimKiem_GiamSoLuong(ByVal sDTO As SachDTO) As Sach
        Dim db As New QuanLyNhaSachDataContext
        Dim book = (From s In db.Saches _
                             Where s.TenSach = sDTO.TenSach And s.TenTG = sDTO.TenTG And s.MaTL = sDTO.MaTL _
                             Select s).Single
        Try
            sDTO.MaSach = book.MaSach
            sDTO.SoLuong = book.Soluong - sDTO.SoLuong
            book.Soluong = sDTO.SoLuong

            db.SubmitChanges()
        Catch ex As Exception
        End Try
        Return book
    End Function

    Public Sub CapNhat(ByVal sDTO As SachDTO)
        Dim db As New QuanLyNhaSachDataContext
        Dim sach = (From s In db.Saches _
                   Where s.MaSach = sDTO.MaSach
                   Select s).Single

        sach.MaTL = sDTO.MaTL
        sach.TenTG = sDTO.TenTG
        sach.Soluong = sDTO.SoLuong

        db.SubmitChanges()
    End Sub

    Public Function SoLuongTon(ByVal sDTO As SachDTO) As Integer
        Dim db As New QuanLyNhaSachDataContext
        Dim s = db.Saches.Single(Function(p) p.TenSach = sDTO.TenSach And p.MaTL = sDTO.MaTL And p.TenTG = sDTO.TenTG)
        Return s.Soluong
    End Function
End Class
